"""卖家会话DAO"""

from datetime import datetime
from typing import List, Optional
from sqlalchemy import select, update, delete, and_, or_, desc, func
from sqlalchemy.ext.asyncio import AsyncSession

from module_admin.entity.do.seller_conversation_do import SellerConversation
from utils.log_util import logger


class SellerConversationDao:
    """卖家会话数据访问对象"""

    @classmethod
    async def create_or_update_conversation(
        cls,
        db: AsyncSession,
        client_id: str,
        conversation_id: str,
        chat_id: Optional[str] = None,
        seller_id: Optional[str] = None,
        seller_name: Optional[str] = None,
        seller_avatar: Optional[str] = None,
        item_id: Optional[str] = None,
        item_title: Optional[str] = None,
        item_image: Optional[str] = None,
        item_price: Optional[str] = None,
        item_tags: Optional[str] = None,  # JSON字符串
        item_images: Optional[str] = None,  # JSON字符串
        video_info: Optional[str] = None,  # JSON字符串
        item_status: Optional[int] = None,
        item_status_str: Optional[str] = None,
        last_message: Optional[str] = None,
        last_message_time: Optional[datetime] = None,
        last_message_direction: Optional[str] = None,
        unread_count: int = 0,
    ) -> SellerConversation:
        """创建或更新会话"""
        try:
            # 查找已存在的会话
            # ✅ 增强查找逻辑：匹配 conversation_id OR chat_id OR (seller_id + item_id)
            conditions = [
                SellerConversation.conversation_id == conversation_id,
            ]
            if chat_id:
                conditions.append(SellerConversation.chat_id == chat_id)
            if seller_id and item_id:
                conditions.append(and_(
                    SellerConversation.seller_id == seller_id,
                    SellerConversation.item_id == item_id
                ))
            
            query = select(SellerConversation).where(
                and_(
                    SellerConversation.client_id == client_id,
                    or_(*conditions),
                    SellerConversation.del_flag == '0'  # 排除已删除的记录
                )
            ).order_by(
                # 优先匹配有chat_id的记录
                desc(SellerConversation.chat_id != ''),
                desc(SellerConversation.chat_id.isnot(None)),
                desc(SellerConversation.last_message_time)
            ).limit(1)

            result = await db.execute(query)
            conversation = result.scalar_one_or_none()
            
            now = datetime.now()
            
            if conversation:
                # 更新已存在的会话
                if chat_id and not conversation.chat_id:
                    conversation.chat_id = chat_id
                
                # ✅ 智能更新 conversation_id (带冲突检测和合并)
                if conversation_id and conversation_id != conversation.conversation_id:
                    # 1. 检查目标 conversation_id 是否已被其他记录占用（包括已删除的记录，因为唯一索引通常不忽略删除标记）
                    check_query = select(SellerConversation).where(
                        and_(
                            SellerConversation.conversation_id == conversation_id,
                            SellerConversation.client_id == client_id,
                            SellerConversation.id != conversation.id
                        )
                    )
                    check_res = await db.execute(check_query)
                    target_conv = check_res.scalar_one_or_none()

                    if target_conv:
                        # 🚨 冲突检测：目标ID已存在！执行合并策略：Current -> Target
                        logger.info(f"⚠️ 会话ID冲突: 现有={conversation.conversation_id}, 目标={conversation_id} 已存在(ID={target_conv.id}, Del={target_conv.del_flag}) -> 执行合并")
                        
                        from module_admin.entity.do.seller_message_do import SellerMessage
                        
                        # A. 迁移消息
                        stmt = update(SellerMessage).where(
                            and_(
                                SellerMessage.client_id == client_id,
                                SellerMessage.conversation_id == conversation.conversation_id
                            )
                        ).values(
                            conversation_id=target_conv.conversation_id,
                            chat_id=target_conv.chat_id if target_conv.chat_id else SellerMessage.chat_id
                        )
                        await db.execute(stmt)
                        
                        # B. 数据补全：如果Target缺少关键信息，从Current复制
                        if conversation.chat_id and not target_conv.chat_id:
                            target_conv.chat_id = conversation.chat_id
                        if conversation.item_id and not target_conv.item_id:
                            target_conv.item_id = conversation.item_id
                        if conversation.seller_id and not target_conv.seller_id:
                            target_conv.seller_id = conversation.seller_id
                        
                        # C. 软删除当前会话
                        conversation.del_flag = '2'  # Merged
                        conversation.updated_at = now
                        
                        # D. 复活目标会话（如果它之前被删除了）
                        if target_conv.del_flag != '0':
                            target_conv.del_flag = '0'
                            target_conv.updated_at = now
                            logger.info(f"♻️ 复活目标会话: {target_conv.conversation_id}")
                        
                        # E. 切换指针：后续更新操作都针对 target_conv
                        conversation = target_conv
                        logger.info(f"✅ 会话合并完成，切换到目标会话: {conversation.conversation_id}")
                        
                    else:
                        # 无冲突，执行原有逻辑
                        is_existing_temp = '#' in str(conversation.conversation_id)
                        is_input_temp = '#' in str(conversation_id)
                        
                        if is_existing_temp and not is_input_temp:
                            conversation.conversation_id = conversation_id
                            logger.info(f"会话ID升级: {conversation.id}, {is_existing_temp} -> {conversation_id}")
                        elif not is_existing_temp and is_input_temp:
                            # 保持现有正式ID，忽略临时ID输入
                            pass
                        else:
                            # 同级更新
                            conversation.conversation_id = conversation_id

                if seller_id:
                    conversation.seller_id = seller_id
                
                # ✅ 自动合并重复会话策略
                # 场景：数据库中存在多条同Item同Seller的记录（可能是由于之前的Bug产生的）
                # 动作：将它们的消息迁移到当前的主会话，并标记旧会话为删除
                if seller_id and item_id:
                    # 查找其他重复会话（排除当前主会话）
                    dup_query = select(SellerConversation).where(
                        and_(
                            SellerConversation.client_id == client_id,
                            SellerConversation.seller_id == seller_id,
                            SellerConversation.item_id == item_id,
                            SellerConversation.id != conversation.id,
                            SellerConversation.del_flag == '0'
                        )
                    )
                    dup_result = await db.execute(dup_query)
                    duplicates = dup_result.scalars().all()
                    
                    if duplicates:
                        try:
                            from module_admin.entity.do.seller_message_do import SellerMessage
                            logger.info(f"🔎 发现 {len(duplicates)} 个重复会话，正在合并到主会话: {conversation.conversation_id}")
                            
                            for dup in duplicates:
                                logger.info(f"   -> 合并会话: {dup.conversation_id} (ID: {dup.id})")
                                # 1. 迁移消息到主会话
                                # 将属于旧会话的消息的 conversation_id 更新为主会话的 ID
                                stmt = update(SellerMessage).where(
                                    and_(
                                        SellerMessage.client_id == client_id,
                                        SellerMessage.conversation_id == dup.conversation_id
                                    )
                                ).values(
                                    conversation_id=conversation.conversation_id,
                                    chat_id=conversation.chat_id if conversation.chat_id else SellerMessage.chat_id
                                )
                                await db.execute(stmt)
                                
                                # 2. 软删除重复会话
                                dup.del_flag = '2' # 使用 '2' 标记为"被合并删除"
                                dup.update_time = now
                            
                            logger.info("✅ 重复会话合并完成")
                        except Exception as merge_e:
                            logger.error(f"⚠️ 合并重复会话失败: {merge_e}", exc_info=True)
                            # 合并失败不应阻断主流程，继续执行
                
                # ✅ 改进：使用 is not None 而不是 if seller_name，允许清空值
                if seller_name is not None:
                    conversation.seller_name = seller_name
                if seller_avatar is not None:
                    conversation.seller_avatar = seller_avatar
                if item_id:
                    conversation.item_id = item_id
                if item_title is not None:
                    conversation.item_title = item_title
                if item_image is not None:
                    conversation.item_image = item_image
                if item_price is not None:
                    conversation.item_price = item_price
                # ✅ 新增：更新扩展商品信息
                should_update_timestamp = False
                if item_tags is not None:
                    conversation.item_tags = item_tags
                    should_update_timestamp = True
                if item_images is not None:
                    conversation.item_images = item_images
                    should_update_timestamp = True
                if video_info is not None:
                    conversation.video_info = video_info
                    should_update_timestamp = True
                if item_status is not None:
                    conversation.item_status = item_status
                    should_update_timestamp = True
                if item_status_str is not None:
                    conversation.item_status_str = item_status_str
                    should_update_timestamp = True
                
                # ✅ 如果更新了商品信息，记录更新时间
                if should_update_timestamp:
                    conversation.item_info_updated_at = now
                if last_message is not None:
                    conversation.last_message = last_message
                if last_message_time:
                    conversation.last_message_time = last_message_time
                if last_message_direction:
                    conversation.last_message_direction = last_message_direction
                if unread_count >= 0:
                    conversation.unread_count = unread_count
                
                conversation.updated_at = now
                conversation.update_time = now
                
            else:
                # 创建新会话
                conversation = SellerConversation(
                    conversation_id=conversation_id,
                    chat_id=chat_id,
                    client_id=client_id,
                    seller_id=seller_id or "",
                    seller_name=seller_name,
                    seller_avatar=seller_avatar,
                    item_id=item_id,
                    item_title=item_title,
                    item_image=item_image,
                    item_price=item_price,
                    # ✅ 新增：保存扩展商品信息
                    item_tags=item_tags,
                    item_images=item_images,
                    video_info=video_info,
                    item_status=item_status,
                    item_status_str=item_status_str,
                    # ✅ 如果提供了商品详情，设置更新时间
                    item_info_updated_at=now if (item_tags or item_images or video_info) else None,
                    unread_count=unread_count,
                    last_message=last_message,
                    last_message_time=last_message_time or now,
                    last_message_direction=last_message_direction,
                    created_at=now,
                    updated_at=now,
                    create_time=now,
                    update_time=now,
                    del_flag='0',
                )
                db.add(conversation)
            
            await db.commit()
            await db.refresh(conversation)
            return conversation
            
        except Exception as e:
            await db.rollback()
            logger.error(f"创建或更新会话失败: {e}", exc_info=True)
            raise

    @classmethod
    async def get_conversation_by_id(
        cls, db: AsyncSession, conversation_id: str, client_id: str
    ) -> Optional[SellerConversation]:
        """根据会话ID获取会话"""
        try:
            query = select(SellerConversation).where(
                and_(
                    SellerConversation.conversation_id == conversation_id,
                    SellerConversation.client_id == client_id,
                    SellerConversation.del_flag == '0'
                )
            )
            result = await db.execute(query)
            return result.scalar_one_or_none()
        except Exception as e:
            logger.error(f"获取会话失败: {e}")
            return None

    @classmethod
    async def get_conversation_by_chat_id(
        cls, db: AsyncSession, chat_id: str, client_id: str
    ) -> Optional[SellerConversation]:
        """根据chat_id获取会话"""
        try:
            query = select(SellerConversation).where(
                and_(
                    SellerConversation.chat_id == chat_id,
                    SellerConversation.client_id == client_id,
                    SellerConversation.del_flag == '0'
                )
            )
            result = await db.execute(query)
            return result.scalar_one_or_none()
        except Exception as e:
            logger.error(f"获取会话失败: {e}")
            return None

    @classmethod
    async def list_conversations(
        cls,
        db: AsyncSession,
        client_id: str,
        page: int = 1,
        limit: int = 50
    ) -> List[SellerConversation]:
        """获取会话列表"""
        try:
            offset = (page - 1) * limit
            
            query = (
                select(SellerConversation)
                .where(
                    and_(
                        SellerConversation.client_id == client_id,
                        SellerConversation.del_flag == '0'
                    )
                )
                .order_by(desc(SellerConversation.last_message_time))
                .offset(offset)
                .limit(limit)
            )
            
            result = await db.execute(query)
            return result.scalars().all()
            
        except Exception as e:
            logger.error(f"获取会话列表失败: {e}")
            return []

    @classmethod
    async def update_unread_count(
        cls, db: AsyncSession, conversation_id: str, client_id: str, increment: int = 1
    ):
        """更新未读数"""
        try:
            query = (
                update(SellerConversation)
                .where(
                    and_(
                        SellerConversation.conversation_id == conversation_id,
                        SellerConversation.client_id == client_id
                    )
                )
                .values(
                    unread_count=SellerConversation.unread_count + increment,
                    updated_at=datetime.now()
                )
            )
            await db.execute(query)
            await db.commit()
        except Exception as e:
            await db.rollback()
            logger.error(f"更新未读数失败: {e}")

    @classmethod
    async def clear_unread_count(
        cls, db: AsyncSession, conversation_id: str, client_id: str
    ):
        """清空未读数"""
        try:
            query = (
                update(SellerConversation)
                .where(
                    and_(
                        SellerConversation.conversation_id == conversation_id,
                        SellerConversation.client_id == client_id
                    )
                )
                .values(unread_count=0, updated_at=datetime.now())
            )
            await db.execute(query)
            await db.commit()
        except Exception as e:
            await db.rollback()
            logger.error(f"清空未读数失败: {e}")

    @classmethod
    async def merge_all_duplicates(cls, db: AsyncSession) -> dict:
        """
        扫描并合并所有重复会话（一次性修复工具）
        返回合并统计信息
        """
        try:
            # 1. 获取所有未删除的会话
            query = select(SellerConversation).where(SellerConversation.del_flag == '0')
            result = await db.execute(query)
            all_convs = result.scalars().all()
            
            # 2. 分组
            groups = {}
            for conv in all_convs:
                # 必须有 item_id 和 seller_id 才参与去重
                if not conv.item_id or not conv.seller_id:
                    continue
                    
                key = (conv.client_id, conv.seller_id, conv.item_id)
                if key not in groups:
                    groups[key] = []
                groups[key].append(conv)
            
            merged_count = 0
            deleted_count = 0
            
            from module_admin.entity.do.seller_message_do import SellerMessage
            
            # 3. 处理重复组
            for key, convs in groups.items():
                if len(convs) <= 1:
                    continue
                
                # 排序规则：有chat_id优先 > 更新时间晚优先 > ID大优先
                # 我们希望保留的是排在第一位的
                convs.sort(key=lambda x: (
                    x.chat_id is not None and x.chat_id != '',  # 有chat_id
                    x.last_message_time or datetime.min,        # 最新消息时间
                    x.updated_at or datetime.min                # 更新时间
                ), reverse=True)
                
                main_conv = convs[0]
                duplicates = convs[1:]
                
                logger.info(f"🔎 发现重复组: Client={key[0]}, Seller={key[1]}, Item={key[2]}")
                logger.info(f"   -> 保留主会话: {main_conv.conversation_id} (ChatID: {main_conv.chat_id})")
                
                for dup in duplicates:
                    logger.info(f"   -> 合并删除: {dup.conversation_id} (ChatID: {dup.chat_id})")
                    
                    # 迁移消息
                    stmt = update(SellerMessage).where(
                        and_(
                            SellerMessage.client_id == main_conv.client_id,
                            SellerMessage.conversation_id == dup.conversation_id
                        )
                    ).values(
                        conversation_id=main_conv.conversation_id,
                        chat_id=main_conv.chat_id if main_conv.chat_id else SellerMessage.chat_id
                    )
                    await db.execute(stmt)
                    
                    # 软删除
                    dup.del_flag = '2'
                    dup.updated_at = datetime.now()
                    deleted_count += 1
                
                merged_count += 1
            
            await db.commit()
            return {"merged_groups": merged_count, "deleted_conversations": deleted_count}
            
        except Exception as e:
            await db.rollback()
            logger.error(f"批量合并重复会话失败: {e}", exc_info=True)
            return {"error": str(e)}

